package com.xm.dao.reception_desk;

import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Account的Dao层
 *
 * @author 康文程
 * @date 2023-12-12 下午 04:57
 */
public class AccountDao extends BaseUtil {
    /**
     * 根据账号和密码查询账户信息
     * @param account 账号，可以是 account_id、phone 或者 mailbox
     * @param password 密码
     * @return 返回 账号信息表id 表示账号和密码匹配，返回 false 表示不匹配或者查询失败
     */
    public String checkAccount(String account, String password) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT account_id, state FROM account WHERE (account_id=? OR phone=? OR mailbox=?) AND password=?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, account, account, account, password);
            if (rs.next()) {
                int accountId = rs.getInt("account_id");
                int state = rs.getInt("state");
                return (state == 0) ? String.valueOf(accountId) : "-1";
            }
        } catch (SQLException e) {
            throw new SqlErrorException("登录失败", e);
        } finally {
            close(rs);
        }
        return "-1";
    }

    /**
     * 手机号和密码注册
     * @param phone 手机号
     * @param password 密码
     * @return 新增Id
     * @throws SqlErrorException 错误抛出
     */
    public Integer addPhoneAccount(String phone, String password) throws SqlErrorException {
        @Language("sql")
        String sql = "INSERT INTO account (phone, password) VALUES (?, ?)";
        try {
            return executeUpdate(sql, phone, password);
        } catch (SQLException e) {
            throw new SqlErrorException("新增账户失败", e);
        }
    }

    /**
     * 邮箱和密码注册
     *
     * @param mailbox  邮箱
     * @param password 密码
     * @return 新增Id
     * @throws SqlErrorException 错误抛出
     */
    public Integer addMailboxAccount(String mailbox, String password) throws SqlErrorException {
        @Language("sql")
        String sql = "INSERT INTO account (mailbox, password) VALUES (?, ?)";
        try {
            return executeUpdate(sql, mailbox, password);
        } catch (SQLException e) {
            throw new SqlErrorException("新增账户失败", e);
        }
    }

    /**
     * 邮箱已存在
     * @param mailbox 邮箱
     * @return false 不存在 true 存在
     * @throws SqlErrorException 抛出SQL语法错误
     */
    public boolean checkMailboxExist(String mailbox) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM account WHERE mailbox = ?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, mailbox);
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询邮箱失败", e);
        } finally {
            close(rs);
        }
        return false;
    }

    /**
     * 手机号是否已存在
     * @param phone 手机号
     * @return true存在 false不存在
     * @throws SqlErrorException 抛出错误
     */
    public boolean checkPhoneExist(String phone) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM account WHERE phone = ?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, phone);
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询手机号失败", e);
        } finally {
            close(rs);
        }
        return false;
    }
    /**
     * 用户订单数量
     * @param informationId 用户ID
     * @return 订单数量
     * @throws SqlErrorException 抛出错误
     */
    public int count(int informationId) throws SqlErrorException {
        @Language("SQL")
        String sql = "SELECT count(o.address_id) FROM address a" +
                " join account_information c on a.information_id=c.information_id" +
                " join orders o on o.address_id=a.address_id " +
                " WHERE a.information_id = ? and orders_status<9";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, informationId);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询手机号失败", e);
        } finally {
            close(rs);
        }
        return 0;
    }

    /**
     * 手机号是否已存在
     * @param phone 手机号
     * @param password 密码
     * @return true存在 false不存在
     * @throws SqlErrorException 抛出错误
     */
    public boolean account(String phone,String password,int informationId) throws SqlErrorException {
        @Language("SQL")
        String sql = "SELECT COUNT(*) FROM account c" +
                " join address a on a.address_id=c.account_id" +
                " WHERE phone =?  and password=? and information_id = ?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, phone,password,informationId);
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询手机号失败", e);
        } finally {
            close(rs);
        }
        return false;
    }



    /**
     * 用户注销
     * @param informationId 用户编号
     * @return true删除成功 false删除失败
     * @throws SqlErrorException 抛出错误
     */
    public boolean accountDelete(int informationId) throws SqlErrorException {
        @Language("SQL")
        String sql = "DELETE FROM account_information WHERE information_id = ?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, informationId);
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询手机号失败", e);
        } finally {
            close(rs);
        }
        return false;
    }
}
